package vnu;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import vocabulary.MyDatabaseVoca;

public class DBAdapter implements MyDatabaseVoca {
	
	private static java.sql.Connection conn;
	private static Statement statement;
	public DBAdapter(){
		// initial Data base in here
		String connection = URL+MYSQL_DB+CHARSET;
	
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				try {
					conn = DriverManager.getConnection(connection, MYSQL_USER, MYSQL_PASSWORD);
					System.out.println("Open connect to database!");
					statement=conn.createStatement();
					createTable();
					
					
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			
	
		
	}
	
	public Record readRecord(int id){
		Record temp = null;
		String readStatement = "select * from "+ MYSQL_TABLE + " where "+ ID + "=" + id;
		try {
			ResultSet result = statement.executeQuery(readStatement);
			while(result.next()){
				temp = new Record(result.getString(1), result.getInt(2));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return temp;
	}
	
	public void addRecord(Record newrecord){
		String InsertStatement = "insert into " + MYSQL_TABLE +"("+NAME+","+AGE+") values('"+ 
								newrecord.getName() +"','"+
								newrecord.getAge() + "')";
		try {
			statement.executeUpdate(InsertStatement);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public void deleteRecord(int id){
		String deleteStatement = "delete from "+ MYSQL_TABLE +" where "+ ID +"=" + id;
		try {
			statement.executeUpdate(deleteStatement);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public void updateRecord(int id,Record newrecord){
		String updateStatement = "update " + MYSQL_TABLE +" set "+
									NAME + "=" + newrecord.getName()+
									AGE + "=" + newrecord.getAge()+ " where " + ID + "="+ id;
		try {
			statement.executeUpdate(updateStatement);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void createTable(){
		String createString = "create table if not exists "+ MYSQL_TABLE + "("+
	            ID  + " int not null auto_increment primary key, "+
				NAME+ " varchar(100)," +
				AGE + " int(5)) ENGINE=MyISAM character set 'utf8'";
		System.out.println(createString);
		try {
			statement.executeUpdate(createString);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("create table: "+ e.getMessage());
		}
	}

	
	public void closeAll(){
		try {
			statement.close();
			conn.close();
			System.out.println("close connect to database!");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
	}
	
	
	public static void main(String arg[]){
		DBAdapter test = new DBAdapter();
		test.addRecord(new Record("Nguyễn  Văn Quân", 25));
		test.deleteRecord(1);
		test.closeAll();
	}
}
